﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

using System.Text;
using System.Data;

using System.Data.OleDb;
using System.Data.Odbc;
using System.Data.SqlClient;
 

namespace myStackSandbox
{
    public partial class _Default : System.Web.UI.Page
    {
        String connStr = "Data Source=myStack2.db.4256415.hostedresource.com;"
                   + "Initial Catalog=myStack2;"
                   + "Persist Security Info=True;"
                   + "User ID=myStack2;Password=P@dres!210";
        SqlConnection conn;
        SqlCommand command;

        protected void Page_Load(object sender, EventArgs e)
        {
            // http://en.csharp-online.net/Working_with_Data%E2%80%94Connecting_to_a_SQL_Server_Database
            // http://www.dotnetspider.com/resources/287-Connecting-Database-accessing-e-records-i.aspx
            // create an open the connection

			this.InitConnection();
     
            // create a SqlCommand object for this connection
            command.CommandText = "Select * from stack";
            command.CommandType = CommandType.Text;
     
            // execute the command that returns a SqlDataReader
            SqlDataReader reader = command.ExecuteReader();
     
            // display the results
            while (reader.Read()) {
               string output = reader["entry_title"].ToString();
               Console.WriteLine(output);
            }
     
            // close the connection
            reader.Close();
            conn.Close();
            
        }

        //	Push

        protected void PushButton_Click(object sender, EventArgs e)
        {
            this.PushItemOntoStack(this.TitleTextBox.Text, this.ContentTextBox.Text);
        }

        private void PushItemOntoStack(string title, string content)
        {
			this.InitConnection();

            if (title != "" && content != "")
            {
                command.CommandText = "INSERT INTO stack (entry_title, entry_content) values ('" + title + "', '" + content + "')";
                command.ExecuteNonQuery();                
            }

			this.conn.Close();
        }


        //	View Stack

        protected void DisplayStackButton_Click(object sender, EventArgs e)
        {
            this.StackListBox.DataSource = this.GetStackFromDb();
			this.StackListBox.DataBind();
        }

        private object GetStackFromDb()
        {
			this.InitConnection();

			// create a SqlCommand object for this connection
			command.CommandText = "Select * from stack";
			command.CommandType = CommandType.Text;

			// execute the command that returns a SqlDataReader
			SqlDataReader reader = command.ExecuteReader();

			List<StackItem> items = new List<StackItem>();

			// display the results
			while (reader.Read())
			{
				string title = reader["entry_title"].ToString();
				int id = (int)reader["entry_id"];
				string content = reader["entry_content"].ToString();

				StackItem item = new StackItem();

				item.Id = id;
				item.Title = title;
				item.Content = content;

				items.Add(item);
			}

			// close the connection
			reader.Close();

			conn.Close();

			return items.ToArray();
        }


        //	View stack item content

        protected void StackListBox_SelectedIndexChanged(object sender, EventArgs e)
        {
			int itemId = Convert.ToInt32(this.StackListBox.SelectedValue);

			this.SelectedStackItemContentLabel.Text = this.GetStackItemContent(itemId);
        }

        private string GetStackItemContent(int itemId)
        {
			this.InitConnection();

			// create a SqlCommand object for this connection
			command.CommandText = String.Format(
				"Select entry_content from stack where entry_id={0}", itemId);
			command.CommandType = CommandType.Text;

			// execute the command that returns a SqlDataReader
			SqlDataReader reader = command.ExecuteReader();

			reader.Read();

			string content = reader["entry_content"].ToString();

			return content;
        }

		private void InitConnection()
		{
			conn = new SqlConnection(connStr);
			conn.Open();
			command = conn.CreateCommand();
		}
    }

	public class StackItem
	{
		public int Id { get; set; }
		public string Title { get; set; }
		public string Content { get; set; }
	}
}
